﻿using DotNetExtensions.AspNet.SQLites.Attributes;
using DotNetExtensions.AspNet.SQLites.Enums;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Linq.Expressions;
using System.Text;

namespace DotNetExtensions.AspNet.SQLites
{
    public class SQLiteCommandBuilder : Singleton<SQLiteCommandBuilder>
    {

        private SQLiteCommandBuilder()
        {

        }
        public CommandText ParseQueryCommandText<TEntity>(Expression<Func<TEntity, bool>> expression) where TEntity : class
        {
            lock (mutex)
            {
                var builder = new SQLiteExpressionBuilder<TEntity>();
                var tableInfo = TableInfoFactory.Default.Get<TEntity>();
                string commandText = $"SELECT * FROM {tableInfo.TableName}";
                if (expression != null)
                {
                    string where = builder.Where(expression).Build();
                    if (!string.IsNullOrWhiteSpace(where))
                    {
                        commandText = $"{commandText} WHERE {where}";
                    }
                }


                return new CommandText(commandText, builder.Parameters.All);
            }
        }
        public CommandText ParseQueryCommandText<TEntity, TProperty>(Expression<Func<TEntity, bool>> expression, Expression<Func<TEntity, TProperty>> orderBy) where TEntity : class
        {
            lock (mutex)
            {
                var builder = new SQLiteExpressionBuilder<TEntity>();
                var tableInfo = TableInfoFactory.Default.Get<TEntity>();
                string commandText = $"SELECT * FROM {tableInfo.TableName}";
                if (expression != null)
                {
                    string where = builder.Where(expression).OrderBy(orderBy, SQLiteOrderBy.Desc).Build();
                    if (!string.IsNullOrWhiteSpace(where))
                    {
                        commandText = $"{commandText} WHERE {where}";
                    }
                }


                return new CommandText(commandText, builder.Parameters.All);
            }
        }
        public PagerCommandText ParseQueryCommandText<TEntity, TProperty>(Expression<Func<TEntity, bool>> expression, int offset, int count, Expression<Func<TEntity, TProperty>> orderBy = null) where TEntity : class
        {
            lock (mutex)
            {
                var builder = new SQLiteExpressionBuilder<TEntity>();
                var tableInfo = TableInfoFactory.Default.Get<TEntity>();
                string commandText = $"SELECT * FROM {tableInfo.TableName}";
                string commandCountText = $"SELECT count(0) FROM {tableInfo.TableName}";
                if (expression != null)
                {
                    string where = builder.Where(expression).OrderBy(orderBy, SQLiteOrderBy.Desc).Build();
                    if (!string.IsNullOrWhiteSpace(where))
                    {
                        commandText = $"{commandText} WHERE {where}";
                        commandCountText = $"{commandCountText} WHERE {where}";
                    }
                }

                if (count > 0)
                {
                    commandText = $"{commandText} LIMIT {count} offset {offset}";
                }


                return new PagerCommandText(commandText, commandCountText, builder.Parameters.All);
            }
        }

        public SQLiteCommand BindCommandValue<TEntity>(TEntity obj, SQLiteCommand command, string commandText)
        {
            lock (mutex)
            {
                command.CommandText = commandText;
                var properties = typeof(TEntity).GetProperties();
                foreach (var item in properties)
                {
                    object value = item.GetValue(obj, null);
                    var attribute = item.GetCustomAttribute<SQLiteColumnAttribute>();
                    if (value != null && attribute != null && attribute.IsJson)
                    {
                        value = JsonConvert.SerializeObject(value);
                    }
                    else if (value != null && attribute != null && attribute.IsType)
                    {
                        Type type = (Type)value;

                        TypeValue typeValue = new TypeValue();
                        typeValue.Clazz = type.FullName;
                        typeValue.Module = type.Module.Name;
                        value = JsonConvert.SerializeObject(typeValue);
                    }
                    if (value == null) value = DBNull.Value;
                    command.Parameters.AddWithValue("$" + item.Name, value);
                }
                return command;
            }
        }

        public SQLiteCommand BindCommandValue<TEntity>(TEntity obj, SQLiteCommand command, string commandText, Dictionary<string, object> parameters)
        {
            lock (mutex)
            {
                command.CommandText = commandText;
                var properties = typeof(TEntity).GetProperties();
                foreach (var item in properties)
                {
                    object value = item.GetValue(obj, null);
                    var attribute = item.GetCustomAttribute<SQLiteColumnAttribute>();
                    if (value != null && attribute != null && attribute.IsJson)
                    {
                        value = JsonConvert.SerializeObject(value);
                    }
                    else if (value != null && attribute != null && attribute.IsType)
                    {
                        Type type = (Type)value;

                        TypeValue typeValue = new TypeValue();
                        typeValue.Clazz = type.FullName;
                        typeValue.Module = type.Module.Name;
                        value = JsonConvert.SerializeObject(typeValue);
                    }
                    if (value == null) value = DBNull.Value;
                    command.Parameters.AddWithValue("$" + item.Name, value);
                }
                if (parameters != null)
                {
                    foreach (var item in parameters)
                    {
                        command.Parameters.AddWithValue(item.Key, item.Value);
                    }
                }
                return command;
            }
        }



        public CommandText ParseCountCommandText<TEntity>(Expression<Func<TEntity, bool>> expression) where TEntity : class
        {
            string commandText;
            var tableInfo = TableInfoFactory.Default.Get<TEntity>();
            var builder = new SQLiteExpressionBuilder<TEntity>();
            string where = "";
            if (expression != null)
            {
                where = " WHERE " + builder.Where(expression).Build();
            }

            commandText = $"SELECT COUNT(0) FROM {tableInfo.TableName} {where}";
            return new CommandText(commandText, builder.Parameters.All);
        }

        public CommandText ParseDeleteCommandText<TEntity>(Expression<Func<TEntity, bool>> expression) where TEntity : class
        {
            string commandText;
            var tableInfo = TableInfoFactory.Default.Get<TEntity>();
            var builder = new SQLiteExpressionBuilder<TEntity>();
            string where = "";
            if (expression != null)
            {
                where = " WHERE " + builder.Where(expression).Build();
            }

            commandText = $"DELETE FROM {tableInfo.TableName} {where}";
            return new CommandText(commandText, builder.Parameters.All);
        }

        public CommandText ParseUpdateWhereText<TEntity>(Expression<Func<TEntity, bool>> expression) where TEntity : class
        {
            string commandText;
            var builder = new SQLiteExpressionBuilder<TEntity>();
            string where = "WHERE 1=0";
            if (expression != null) where = " WHERE " + builder.Where(expression).Build();

            commandText = where;
            return new CommandText(commandText, builder.Parameters.All);
        }

        public string ParseUpdateWhereText<TEntity, TProperty>(Expression<Func<TEntity, TProperty>> expression) where TEntity : class
        {
            var tableInfo = TableInfoFactory.Default.Get<TEntity>();
            var builder = new SQLiteExpressionBuilder<TEntity>();
            string commandText = tableInfo.UpdateCommandPlainText;
            string where = "WHERE 1=0";
            if (expression != null)
            {
                List<string> whereKeys = builder.GetExpressionMembers(expression);

                where = " WHERE " + string.Join(" AND ", whereKeys.Select(key => $"{key}={tableInfo.Prefix}{key}"));
            }
            return $"{commandText} {where}";
        }
    }
}
